using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Text;

using EntLibEC.Cmn;
using EntLibEC.Info.Online;

namespace EntLibEC.Dac.Online
{
	/// <summary>
	/// Summary description for SaleAdvDac.
	/// </summary>
	public class SaleAdvDac
	{
		

		public SaleAdvDac()
		{}

		public int InsertMaster(SaleAdvInfo oParam)
		{
			string sql = @"INSERT INTO Ent_SaleAdvertisement
                            (
                            Name, Header, Footer, CssPath, IsGroupByCategory, Status, CreateUserSysNo
                            )
                            VALUES (
                            @Name, @Header, @Footer, @CssPath, @IsGroupByCategory, @Status, @CreateUserSysNo
                            );
							set @SysNo = SCOPE_IDENTITY();";
			
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar, 100);
			SqlParameter paramHeader = new SqlParameter("@Header", SqlDbType.NText);
			SqlParameter paramFooter = new SqlParameter("@Footer", SqlDbType.NVarChar, 1000);
			SqlParameter paramCssPath = new SqlParameter("@CssPath", SqlDbType.NVarChar, 100);
			SqlParameter paramGroupByCategory = new SqlParameter("@IsGroupByCategory", SqlDbType.Int, 4);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);
			SqlParameter paramCreateUserSysNo = new SqlParameter("@CreateUserSysNo", SqlDbType.Int, 4);

			paramSysNo.Direction = ParameterDirection.Output;

			if ( oParam.Name != AppConst.StringNull)
				paramName.Value = oParam.Name;
			else
				paramName.Value = System.DBNull.Value;

			if ( oParam.Header != AppConst.StringNull)
				paramHeader.Value = oParam.Header;
			else
				paramHeader.Value = System.DBNull.Value;

			if ( oParam.Footer != AppConst.StringNull)
				paramFooter.Value = oParam.Footer;
			else
				paramFooter.Value = System.DBNull.Value;

			if ( oParam.CssPath != AppConst.StringNull)
				paramCssPath.Value = oParam.CssPath;
			else
				paramCssPath.Value = System.DBNull.Value;

			if ( oParam.Status != AppConst.IntNull)
				paramStatus.Value = oParam.Status;
			else
				paramStatus.Value = System.DBNull.Value;

			if ( oParam.CreateUserSysNo != AppConst.IntNull)
				paramCreateUserSysNo.Value = oParam.CreateUserSysNo;
			else
				paramCreateUserSysNo.Value = System.DBNull.Value;

			paramGroupByCategory.Value = oParam.IsGroupByCategory ? 1 : 0;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramName);
			cmd.Parameters.Add(paramHeader);
			cmd.Parameters.Add(paramFooter);
			cmd.Parameters.Add(paramCssPath);
			cmd.Parameters.Add(paramGroupByCategory);
			cmd.Parameters.Add(paramStatus);
			cmd.Parameters.Add(paramCreateUserSysNo);

			return SqlHelper.ExecuteNonQuery(cmd, out oParam.SysNo);
		}

		public int InsertItem(SaleAdvItemInfo oParam)
		{
			string sql = @"INSERT INTO Ent_SaleAdvertisementItem
                            (
                            SaleAdvSysNo, ProductSysNo, Priority, Introduction
                            )
                            VALUES (
                            @SaleAdvSysNo, @ProductSysNo, @Priority, @Introduction
                            )";
			
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSaleAdvSysNo = new SqlParameter("@SaleAdvSysNo", SqlDbType.Int, 4);
			SqlParameter paramProductSysNo = new SqlParameter("@ProductSysNo", SqlDbType.Int, 4);
			SqlParameter paramPriority = new SqlParameter("@Priority", SqlDbType.Int, 4);
			SqlParameter paramIntroduction = new SqlParameter("@Introduction", SqlDbType.NVarChar, 1000);

			if ( oParam.SaleAdvSysNo != AppConst.IntNull)
				paramSaleAdvSysNo.Value = oParam.SaleAdvSysNo;
			else
				paramSaleAdvSysNo.Value = System.DBNull.Value;

			if ( oParam.ProductSysNo != AppConst.IntNull)
				paramProductSysNo.Value = oParam.ProductSysNo;
			else
				paramProductSysNo.Value = System.DBNull.Value;

			if ( oParam.Priority != AppConst.IntNull)
				paramPriority.Value = oParam.Priority;
			else
				paramPriority.Value = System.DBNull.Value;

			if ( oParam.Introduction != AppConst.StringNull)
				paramIntroduction.Value = oParam.Introduction;
			else
				paramIntroduction.Value = System.DBNull.Value;

			cmd.Parameters.Add(paramSaleAdvSysNo);
			cmd.Parameters.Add(paramProductSysNo);
			cmd.Parameters.Add(paramPriority);
			cmd.Parameters.Add(paramIntroduction);

			return SqlHelper.ExecuteNonQuery(cmd);
		}

		public int UpdateMaster(SaleAdvInfo oParam)
		{
			string sql = @"UPDATE Ent_SaleAdvertisement SET 
                            Name=@Name, 
                            Header=@Header, Footer=@Footer, 
                            CssPath=@CssPath, IsGroupByCategory=@IsGroupByCategory, Status=@Status
                            WHERE SysNo=@SysNo";
			
			SqlCommand cmd = new SqlCommand(sql);

			SqlParameter paramSysNo = new SqlParameter("@SysNo", SqlDbType.Int,4);
			SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar, 100);
			SqlParameter paramHeader = new SqlParameter("@Header", SqlDbType.NText);
			SqlParameter paramFooter = new SqlParameter("@Footer", SqlDbType.NVarChar, 2000);
			SqlParameter paramCssPath = new SqlParameter("@CssPath", SqlDbType.NVarChar, 100);
			SqlParameter paramGroup = new SqlParameter("@IsGroupByCategory", SqlDbType.Int, 4);
			SqlParameter paramStatus = new SqlParameter("@Status", SqlDbType.Int, 4);

			if ( oParam.SysNo != AppConst.IntNull)
				paramSysNo.Value = oParam.SysNo;
			else
				throw new Exception("SysNo can't be null");

			if ( oParam.Name != AppConst.StringNull)
				paramName.Value = oParam.Name;
			else
				paramName.Value = System.DBNull.Value;

			if ( oParam.Header != AppConst.StringNull)
				paramHeader.Value = oParam.Header;
			else
				paramHeader.Value = System.DBNull.Value;

			if ( oParam.Footer != AppConst.StringNull)
				paramFooter.Value = oParam.Footer;
			else
				paramFooter.Value = System.DBNull.Value;

			if ( oParam.CssPath != AppConst.StringNull)
				paramCssPath.Value = oParam.CssPath;
			else
				paramCssPath.Value = System.DBNull.Value;

			if ( oParam.Status != AppConst.IntNull)
				paramStatus.Value = oParam.Status;
			else
				paramStatus.Value = System.DBNull.Value;

			paramGroup.Value = oParam.IsGroupByCategory ? 1 : 0;

			cmd.Parameters.Add(paramSysNo);
			cmd.Parameters.Add(paramName);
			cmd.Parameters.Add(paramHeader);
			cmd.Parameters.Add(paramFooter);
			cmd.Parameters.Add(paramCssPath);
			cmd.Parameters.Add(paramGroup);
			cmd.Parameters.Add(paramStatus);

			return SqlHelper.ExecuteNonQuery(cmd);
		}

		public int Delete(int sysno)
		{
			string sql = "DELETE Ent_SaleAdvertisement WHERE SysNo = " + sysno;
			return SqlHelper.ExecuteNonQuery(sql);
		}

		public int DeleteItem(int sysno)
		{
			string sql = "DELETE Ent_SaleAdvertisementItem WHERE SysNo = " + sysno;
			return SqlHelper.ExecuteNonQuery(sql);
		}

		public int DeleteItemBySaleAdvId(int sysno)
		{
			string sql = "DELETE Ent_SaleAdvertisementItem WHERE SaleAdvSysNo = " + sysno;
			return SqlHelper.ExecuteNonQuery(sql);
		}
	}
}
